﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spBkp_ShouldTakeFull]
#-- Purpose:		Guestimates what the size of a differential backup would
#--					be and determines if a full backup should be performed
#--					instead.
#--	Last Update:	09/19/2016
#--					For a complete history - please review comments in Version
#--					Control.
#-- Called By:		[dbo].[spBkp_Differential]
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spBkp_ShouldTakeFull]
(
	@database_name	sysname,
	@full_pct		int		= 75
)
AS

SET NOCOUNT ON

IF EXISTS (	SELECT		database_backup_lsn
			FROM		[msdb].[dbo].[backupset]
			WHERE		backup_set_id = (
										SELECT		MAX(backup_set_id)
										FROM		[msdb].[dbo].[backupset]
										WHERE		database_name = @database_name
										)
						AND database_backup_lsn = 0
	)
  BEGIN
	--- The recovery path is currently broken.  Taking a full backup is the best thing to 
	--- do in order to restore the recovery path.
	RETURN CAST(1 as bit)
  END

ELSE
  BEGIN
	/*SQL Differential Backup Size Prediction - Doug Zuckerman, 2009 - http://dougzuck.com */
	---   http://dougzuck.com/sql-differential-backup-size-prediction

	--- Remove any existing Temp Tables
	IF ISNULL(OBJECT_ID('[tempdb].[dbo].[##showFileStats]'), 1) <> 1
		DROP TABLE ##showFileStats
	IF ISNULL(OBJECT_ID('[tempdb].[dbo].[##DCM]'), 1) <> 1
		DROP TABLE ##DCM
	IF ISNULL(OBJECT_ID('[tempdb].[dbo].[##DBSize]'), 1) <> 1
		DROP TABLE ##DBSize

	--- Create new, empty Temp Tables 
	CREATE TABLE ##showFileStats 
	(
		fileID INT,
		fileGroup INT,
		totalExtents BIGINT,
		usedExtents BIGINT,
		logicalFileName VARCHAR (500),	
		filePath VARCHAR (1000)
	)
	CREATE TABLE ##DCM 
	(
		parentObject VARCHAR(5000),
		[object] VARCHAR(5000),
		FIELD VARCHAR (5000),
		value VARCHAR (5000)
	)
	CREATE TABLE ##DBSize
	(
		database_name varchar(255),
		db_size float
	)
 
	--- Declare Local Variables
	DECLARE	@currentFileID int, @step int, @totalExtentsOfFile bigint, @currentDCM bigint,
			@SQL varchar(4000), @dbSizeMB float, @diffSizeMB float
	DECLARE myCursor SCROLL CURSOR FOR
		SELECT	fileID,
				totalExtents 
		FROM	##showFileStats

	--- Determine total size of database
	INSERT INTO ##DBSize
		EXEC ('	SELECT	''' + @database_name + ''', SUM(size * CAST(8 as bigint) / 1024)
				FROM	[' + @database_name + '].[sys].[database_files]
				WHERE	type = 0' )

	SELECT	@dbSizeMB = db_size
	FROM	##DBSize
	WHERE	database_name = @database_name

	--- Initialize Local Variables
	SET @step = 511232

	/*we need to get a list of all the files in the database.  each file needs to be looked at*/	
	INSERT INTO ##showFileStats 
		EXEC('USE [' + @database_name + '];DBCC SHOWFILESTATS WITH TABLERESULTS, NO_INFOMSGS')
 
	/*look at each differential change map page in each data file of the database and put the output into ##DCM*/
	OPEN myCursor
	FETCH NEXT FROM myCursor INTO @currentFileID, @totalExtentsOfFile
	WHILE @@FETCH_STATUS = 0 
	  BEGIN
		--- Initialize DCM Location
		SET		@currentDCM = 6
	
		--- Loop through the file
		WHILE	@currentDCM <= (@totalExtentsOfFile * 8)
		BEGIN	
			SET @SQL = 'USE [' + @database_name + '];DBCC PAGE(['+ @database_name + '], ' + CAST(@currentFileID AS VARCHAR) + ', ' + CAST(@currentDCM AS VARCHAR) + ', 3) WITH TABLERESULTS, NO_INFOMSGS'
			INSERT INTO ##DCM 
				EXEC (@SQL)
		
			--- Increment DCM Location
			SET @currentDCM = @currentDCM + @step
		END
 
		--- Move to the next file
		FETCH NEXT FROM myCursor INTO @currentFileID, @totalExtentsOfFile
	  END
	CLOSE myCursor
	DEALLOCATE myCursor
 
	/*remove all unneeded rows from our results table*/
	DELETE	FROM ##DCM 
	WHERE	value = 'NOT CHANGED' 
			OR parentObject NOT LIKE 'DIFF_MAP%'
 
	/*sum the extentTally column*/
	SELECT	@diffSizeMB = SUM(extentTally/16)
	FROM	(
			SELECT		(CASE WHEN secondChangedExtent > 0 
							THEN CAST(secondChangedExtent AS BIGINT) - CAST(firstChangedExtent AS BIGINT) + 1
							ELSE 1 END) extentTally    /*create extentTally column*/
			FROM		(
						SELECT	(SUBSTRING(FIELD,(SELECT CHARINDEX(':', FIELD, 0))+1,(CHARINDEX(')', FIELD, 0))-(CHARINDEX(':', FIELD, 0))-1))/8 AS firstChangedExtent,
								(CASE WHEN CHARINDEX(':', FIELD, CHARINDEX(':', FIELD, 0)+1) > 0 THEN (SUBSTRING(FIELD,(CHARINDEX(':', FIELD, CHARINDEX(':', FIELD, 0)+1)+1),(CHARINDEX(')', FIELD,CHARINDEX(')', FIELD, 0)+1))-(CHARINDEX(':', FIELD, CHARINDEX(':', FIELD, 0)+1))-1))/8
										ELSE '' END) secondChangedExtent
						FROM	##DCM
						) parsedFieldColumn
			) extentTallyColumn
			/*parse the 'field' column to give us the first and last extents of the range*/

	--- Drop Temporary Tables
	IF ISNULL(OBJECT_ID('[tempdb].[dbo].[##showFileStats]'), 1) <> 1
		DROP TABLE ##showFileStats
	IF ISNULL(OBJECT_ID('[tempdb].[dbo].[##DCM]'), 1) <> 1
		DROP TABLE ##DCM
	IF ISNULL(OBJECT_ID('[tempdb].[dbo].[##DBSize]'), 1) <> 1
		DROP TABLE ##DBSize

	--- Return if a full should be performed instead
	IF	(@diffSizeMB / @dbSizeMB) > (CAST(@full_pct as float) / 100)
		RETURN CAST(1 as bit)
	ELSE
		RETURN CAST(0 as bit)
  END

SET NOCOUNT OFF